/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.SecuritiesDao;
import com.feib.stms.dao.jdbc.rowmapper.SecuritiesRowMapper;
import com.feib.stms.model.Securities;
import com.feib.stms.pagination.PaginatedList;
import com.feib.stms.util.UserUtil;

/**
 * @author Jimmy Liu
 *
 */
@Repository("securitiesDao")
public class SecuritiesJdbcDao extends BaseJdbcDao implements SecuritiesDao 
{

	private OracleSequenceMaxValueIncrementer sequence;

	private SimpleJdbcInsert securitiesJdbcInsert;

	@Override
	protected void initDao() throws Exception {
		super.initDao();

		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "SECURITIES_PK_SEQ");
		this.securitiesJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.securitiesJdbcInsert.withTableName("SECURITIES")
			.usingColumns("SECURITIES_PK", "GROUP_PK", "PARENT_SECURITIES_PK", "SECURITIES_NO", "SECURITIES_NAME"
					, "SECURITIES_ADDRESS", "CONTACT_NAME", "CONTACT_EMAIL", "CONTACT_TELNO", "CONTACT_CELLNO"
					, "SECURITIES_NOTE", "UBM"
					, "CREATED_BY", "CREATED_DATE", "UPDATED_DATE", "UPDATED_BY");
	}


	/* (non-Javadoc)
	 * @see com.feib.stms.dao.SecuritiesDao#parentSecurities(java.lang.String)
	 */
	public Securities getBySecuritiesNo(String securitiesNo) {

		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		sql.append("SELECT S.*, G.GROUP_NO, G.GROUP_NAME, G.GROUP_TYPE");
		sql.append(" FROM SECURITIES S, APP_GROUP G");
		sql.append(" WHERE S.SECURITIES_NO = :SECURITIES_NO");
		sql.append(" AND S.GROUP_PK = G.GROUP_PK");
		parameters.addValue("SECURITIES_NO",  securitiesNo, Types.VARCHAR);

		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new SecuritiesRowMapper(this), parameters));
	}

	public Securities getBySecuritiesPk(Long parentSecuritiesPk) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		sql.append("SELECT S.*");
		sql.append(" FROM SECURITIES S, APP_GROUP G");
		sql.append(" WHERE S.SECURITIES_PK = :SECURITIES_PK");
		sql.append(" AND S.GROUP_PK = G.GROUP_PK");
		parameters.addValue("SECURITIES_PK",  parentSecuritiesPk, Types.NUMERIC);

		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new SecuritiesRowMapper(this), parameters));
	}

	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.SecuritiesDao#insert(com.feib.stms.model.Securities)
	 */
	public int insert(Securities securities) {
		int insertCounts = 0;
		try{
			if(securities != null){
				String userId = UserUtil.getUserId();
				securities.setCreatedBy(securities.getSecuritiesNo() + "_" +userId);
				securities.setCreatedDate(new Date());
				securities.setUpdatedBy(securities.getSecuritiesNo() + "_" +userId);
				securities.setUpdatedDate(new Date());
			}
			MapSqlParameterSource parameters = new MapSqlParameterSource();
			Long securitiesPk = new Long(this.sequence.nextLongValue());
			parameters.addValue("SECURITIES_PK",  securitiesPk, Types.NUMERIC);
			parameters.addValue("GROUP_PK", securities.getGroup().getGroupPk(), Types.NUMERIC);
			parameters.addValue("PARENT_SECURITIES_PK", securities.getParentSecurities() == null?securities.getParentSecurities():securities.getParentSecurities().getSecuritiesPk(), Types.NUMERIC);
			parameters.addValue("SECURITIES_NO",  securities.getSecuritiesNo(), Types.VARCHAR);
			parameters.addValue("SECURITIES_NAME",  securities.getSecuritiesName(), Types.VARCHAR);
			parameters.addValue("SECURITIES_ADDRESS",  securities.getSecuritiesAddress(), Types.VARCHAR);
			parameters.addValue("CONTACT_NAME", securities.getContactName(), Types.VARCHAR);
			parameters.addValue("CONTACT_EMAIL", securities.getContactEmail(), Types.VARCHAR);
			parameters.addValue("CONTACT_TELNO", securities.getContactTelno(), Types.VARCHAR);
			parameters.addValue("CONTACT_CELLNO",  securities.getContactCellno(), Types.VARCHAR);
			parameters.addValue("SECURITIES_NOTE",  securities.getSecuritiesNote(), Types.VARCHAR);
			parameters.addValue("UBM",  securities.getUbm(), Types.VARCHAR);
			parameters.addValue("CREATED_BY",  securities.getCreatedBy(), Types.VARCHAR);
			parameters.addValue("CREATED_DATE",  securities.getCreatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_DATE",  securities.getUpdatedDate(), Types.TIMESTAMP);
			parameters.addValue("UPDATED_BY",  securities.getUpdatedBy(), Types.VARCHAR);
						
			insertCounts = securitiesJdbcInsert.execute(parameters);
			
			if (insertCounts > 0)
				securities.setSecuritiesPk(securitiesPk);
			return insertCounts;
		}catch (DataAccessException e) {
			logger.error(e.getMessage(), e);
		}
		return 0;
	}


	public long findParentSecuritiesPk(Long groupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		
		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();
		
		sql.append("SELECT SECURITIES_PK FROM SECURITIES WHERE PARENT_SECURITIES_PK is NULL ");
		sql.append(" AND GROUP_PK = ? ");
		args.add(groupPk);

		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		/* 母公司的PK */
		int tempPk;
		/* 嘗試去查找母公司的PK，如果找不到，則返回-1，另作處理 */
		try {
			tempPk = this.getJdbcTemplate().queryForInt(sqlQueryProvider.getSql(), sqlQueryProvider.getArgs());
		} catch (Exception e) {
			tempPk = -1;
		}
		
		return tempPk;
	}

	/**
	 * 取得查詢結果
	 */
	public PaginatedList<Securities> findForPaginatedListBySecurities(Securities securities, PaginatedList paginatedList) {
		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderBySecurities(securities, paginatedList);
		
		return this.queryForPaginatedList(sqlQueryProvider, new SecuritiesRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());
	}
	
	protected SqlQueryProvider getSqlQueryProviderBySecurities(Securities securities, PaginatedList paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		
		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();
		
		sql.append("select * from SECURITIES where 1=1 AND parent_securities_pk is not null ");
		
		sql.append(" AND GROUP_PK = ? ");
		args.add(securities.getGroup().getGroupPk());
		
		if (null != securities)
		{	
			/* 分公司編號  查詢 */
			if (null != securities.getSecuritiesNo() && !"".equals(securities.getSecuritiesNo())) {				
				sql.append(" AND SECURITIES_NO = ? ");				
				args.add(securities.getSecuritiesNo());							
			}
			
			/* 分公司名稱  查詢 */
			if (null != securities.getSecuritiesName() && !"".equals(securities.getSecuritiesName())) {				
				sql.append(" AND SECURITIES_NAME like ? ");				
				args.add("%" + securities.getSecuritiesName() + "%");							
			}
				
		}
		
		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		
		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		return sqlQueryProvider;
	}


	public Securities findSecuritiesBySecuritiesPk(Long securitiesPk) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();
		
		sql.append("SELECT S.*");
		sql.append(" FROM SECURITIES S");
		sql.append(" WHERE S.securities_pk = :securities_pk");
		parameters.put("securities_pk",  securitiesPk);
		return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new SecuritiesRowMapper(this), parameters);
	}


	public int update(Securities securities) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		sql.append("UPDATE SECURITIES SET SECURITIES_NO = :SECURITIES_NO,SECURITIES_NAME = :SECURITIES_NAME,SECURITIES_ADDRESS = :SECURITIES_ADDRESS ");
		sql.append(",CONTACT_NAME = :CONTACT_NAME,CONTACT_EMAIL = :CONTACT_EMAIL,CONTACT_TELNO = :CONTACT_TELNO,CONTACT_CELLNO = :CONTACT_CELLNO ");
		sql.append(",SECURITIES_NOTE = :SECURITIES_NOTE,UPDATED_DATE = :UPDATED_DATE,UPDATED_BY = :UPDATED_BY ");
		sql.append(" WHERE SECURITIES_PK = :SECURITIES_PK");		
		
		parameters.addValue("SECURITIES_NO",  securities.getSecuritiesNo(), Types.VARCHAR);
		parameters.addValue("SECURITIES_NAME",  securities.getSecuritiesName(), Types.VARCHAR);
		parameters.addValue("SECURITIES_ADDRESS",  securities.getSecuritiesAddress(), Types.VARCHAR);
		parameters.addValue("CONTACT_NAME",  securities.getContactName(), Types.VARCHAR);
		parameters.addValue("CONTACT_EMAIL",  securities.getContactEmail(), Types.VARCHAR);
		parameters.addValue("CONTACT_TELNO",  securities.getContactTelno(), Types.VARCHAR);
		parameters.addValue("CONTACT_CELLNO",   securities.getContactCellno(), Types.VARCHAR);
		parameters.addValue("SECURITIES_NOTE",  securities.getSecuritiesNote(), Types.VARCHAR);
		parameters.addValue("UPDATED_DATE",  securities.getUpdatedDate(), Types.TIMESTAMP);
		parameters.addValue("UPDATED_BY",  securities.getUpdatedBy(), Types.VARCHAR);
		parameters.addValue("SECURITIES_PK",  securities.getSecuritiesPk(), Types.NUMERIC);		
		return this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
	}

	/**
	 * 根據GroupPk取得處理券商資料
	 */
	public List<Securities> findSecuritiesByGroupPk(List<String> userGroupPk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();		
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select * from SECURITIES where 1=1 ");
		
		if(null != userGroupPk && userGroupPk.size()>0){
			sql.append(" AND GROUP_PK IN :GROUP_PK ");
			String tempValue = "";
			int j = userGroupPk.size();
			for (int i = 0 ; i < j ; i++ ) {
				if(i == j-1){
					tempValue += userGroupPk.get(i);
				}else{
					tempValue += userGroupPk.get(i)+", ";
				}
			}
			parameters.put("GROUP_PK", tempValue);
		}
		return this.getSimpleJdbcTemplate().query(sql.toString(), new SecuritiesRowMapper(this), parameters);
	}
	
	/**
	 * 取得所有分公司資料
	 */
	public List<Securities> findAllSecurities() {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();		
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select * from SECURITIES");
		
		return this.getSimpleJdbcTemplate().query(sql.toString(), new SecuritiesRowMapper(this), parameters);
	}


	/*
	 * (non-Javadoc)
	 * @see com.feib.stms.dao.SecuritiesDao#loadAll()
	 */
	public List<Securities> loadAll() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT S.*, G.GROUP_NO, G.GROUP_NAME, G.GROUP_TYPE");
		sql.append(" FROM SECURITIES S, APP_GROUP G");
		sql.append(" WHERE S.GROUP_PK = G.GROUP_PK");
		sql.append(" ORDER BY S.SECURITIES_NO");
		
		return this.getJdbcTemplate().query(sql.toString(), new Object[]{}, new SecuritiesRowMapper(this));
	}


	/**
	 * 根據券商代號取得資料
	 */
	public List<Securities> findSecuritiesBySecuritiesNos(List<String> securitiesNo) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();		
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select * from SECURITIES where 1=1 ");
		
//		 可查詢的分公司
		if (CollectionUtils.isNotEmpty(securitiesNo))
		{		

			if (1 == securitiesNo.size())
			{
				sql.append(" AND SECURITIES_NO = :SECURITIES_NO");
				parameters.put("SECURITIES_NO",securitiesNo.get(0) );
			}
			else
			{
				sql.append(" AND SECURITIES_NO IN (:SECURITIES_NOS)");
				parameters.put("SECURITIES_NOS",securitiesNo );
			}

		}
		return this.getSimpleJdbcTemplate().query(sql.toString(), new SecuritiesRowMapper(this), parameters);
	}

	/**
	 * 根據分公司資料查找PK
	 */
	public Securities findSecuritiesPkBySecurities(Securities securities) {
		if(null != securities){
			StringBuffer sql = new StringBuffer();
			Map<String, Object> parameters = new HashMap<String, Object>();
			
			sql.append("SELECT S.*");
			sql.append(" FROM SECURITIES S WHERE 1=1 ");
			//是否母公司
			if(null != securities.getParentSecurities()){
				if(null != securities.getParentSecurities().getSecuritiesPk() && securities.getParentSecurities().getSecuritiesPk() != 0L ){
					sql.append(" AND S.PARENT_SECURITIES_PK = :PARENT_SECURITIES_PK ");
					parameters.put("PARENT_SECURITIES_PK", securities.getSecuritiesNo());
				}else{
					sql.append(" AND S.PARENT_SECURITIES_PK IS NULL ");
				}
			}else{
				sql.append(" AND S.PARENT_SECURITIES_PK IS NULL ");
			}

			//券商編號
			if(StringUtils.isNotEmpty(securities.getSecuritiesNo())){
				sql.append(" AND S.SECURITIES_NO = :SECURITIES_NO ");
				parameters.put("SECURITIES_NO", securities.getSecuritiesNo());
			}else{
				sql.append(" AND S.SECURITIES_NO IS NULL ");
			}
			//券商名稱
			if(StringUtils.isNotEmpty(securities.getSecuritiesName())){
				sql.append(" AND S.SECURITIES_NAME = :SECURITIES_NAME ");
				parameters.put("SECURITIES_NAME", securities.getSecuritiesName());
			}else{
				sql.append(" AND S.SECURITIES_NAME IS NULL ");
			}
			//券商地址
			if(StringUtils.isNotEmpty(securities.getSecuritiesAddress())){
				sql.append(" AND S.SECURITIES_ADDRESS = :SECURITIES_ADDRESS ");
				parameters.put("SECURITIES_ADDRESS", securities.getSecuritiesAddress());
			}else{
				sql.append(" AND S.SECURITIES_ADDRESS IS NULL ");
			}
			//聯絡人名稱
			if(StringUtils.isNotEmpty(securities.getContactName())){
				sql.append(" AND S.CONTACT_NAME = :CONTACT_NAME ");
				parameters.put("CONTACT_NAME", securities.getContactName());
			}else{
				sql.append(" AND S.CONTACT_NAME IS NULL ");
			}
			//聯絡人EMAIL
			if(StringUtils.isNotEmpty(securities.getContactEmail())){
				sql.append(" AND S.CONTACT_EMAIL = :CONTACT_EMAIL ");
				parameters.put("CONTACT_EMAIL", securities.getContactEmail());
			}else{
				sql.append(" AND S.CONTACT_EMAIL IS NULL ");
			}
			//聯絡人電話
			if(StringUtils.isNotEmpty(securities.getContactTelno())){
				sql.append(" AND S.CONTACT_TELNO = :CONTACT_TELNO ");
				parameters.put("CONTACT_TELNO", securities.getContactTelno());
			}else{
				sql.append(" AND S.CONTACT_TELNO IS NULL ");
			}
			//聯絡人手機
			if(StringUtils.isNotEmpty(securities.getContactCellno())){
				sql.append(" AND S.CONTACT_CELLNO = :CONTACT_CELLNO ");
				parameters.put("CONTACT_CELLNO", securities.getContactCellno());
			}else{
				sql.append(" AND S.CONTACT_CELLNO IS NULL ");
			}
			//券商備註
			if(StringUtils.isNotEmpty(securities.getSecuritiesNote())){
				sql.append(" AND S.SECURITIES_NOTE = :SECURITIES_NOTE ");
				parameters.put("SECURITIES_NOTE", securities.getSecuritiesNote());
			}else{
				sql.append(" AND S.SECURITIES_NOTE IS NULL ");
			}
			//UBM
			if(StringUtils.isNotEmpty(securities.getUbm())){
				sql.append(" AND S.UBM = :UBM ");
				parameters.put("UBM", securities.getUbm());
			}else{
				sql.append(" AND S.UBM IS NULL ");
			}
			return this.getSimpleJdbcTemplate().queryForObject(sql.toString(), new SecuritiesRowMapper(this), parameters);
		}else{
			return null;
		}
		
	}

	/** 查詢SecuritiesNo是否重複 --新增使用
	 * @param SecuritiesNo
	 * @return
	 */
	public int countSecuritiesNo(String securities_No) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("SELECT  COUNT(*)  FROM SECURITIES WHERE SECURITIES_NO = :SECURITIES_NO");
		parameters.put("SECURITIES_NO",  securities_No);
		int groupCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return groupCount;
	}

	/**
	 * 以券商代號及所屬群組Pk查詢
	 * @param securitiesNo 券商代號
	 * @param groupPk 所屬群組Pk
	 * @return
	 */
	public Securities getBySecuritiesNoAndGroupPk(String securitiesNo, Long groupPk) {

		StringBuffer sql = new StringBuffer();
		Map<String, Object> parameters = new HashMap<String, Object>();

		sql.append("SELECT S.*, G.GROUP_NO, G.GROUP_NAME, G.GROUP_TYPE");
		sql.append(" FROM SECURITIES S, APP_GROUP G");
		sql.append(" WHERE S.SECURITIES_NO = :SECURITIES_NO");
		sql.append(" AND S.GROUP_PK = G.GROUP_PK");
		sql.append(" AND G.GROUP_PK = :GROUP_PK");
		parameters.put("SECURITIES_NO",  securitiesNo);
		parameters.put("GROUP_PK",  groupPk);
		
		return DataAccessUtils.singleResult(this.getSimpleJdbcTemplate().query(sql.toString(), new SecuritiesRowMapper(this), parameters));
	}
	
	public PaginatedList<Securities> findForPaginatedListNotBank(Securities securities, PaginatedList paginatedList){
		
		SqlQueryProvider sqlQueryProvider = getSqlQueryProviderBySecuritieNo(securities, paginatedList);
		
		return this.queryForPaginatedList(sqlQueryProvider, new SecuritiesRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());
		
	}
	
	protected SqlQueryProvider getSqlQueryProviderBySecuritieNo(Securities securities, PaginatedList paginatedList)
	{
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		
		/* 查詢條件 */
		List<Object> args = new ArrayList<Object>();
		
		sql.append("SELECT S.* from SECURITIES S, APP_GROUP G  WHERE S.GROUP_PK = G.GROUP_PK");
		sql.append(" AND G.GROUP_TYPE <>1 ");

			//券商編號
			if(StringUtils.isNotEmpty(securities.getSecuritiesNo())){
				String securitiesNo="%"+securities.getSecuritiesNo()+"%";
				sql.append(" AND SECURITIES_NO like ? ");
				args.add(securitiesNo);				
			}
			if(StringUtils.isNotEmpty(securities.getSecuritiesName())){
				sql.append(" AND SECURITIES_NAME LIKE ?");
				args.add("%" + securities.getSecuritiesName() + "%");
			}
		
		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		
		SqlQueryProvider sqlQueryProvider = new SqlQueryProvider(sql.toString(), args);
		return sqlQueryProvider;
	}

	
}

